﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for SaleDAO
/// </summary>
public class SaleDAO:DBAccess, ISaleInfo
{
    #region sql command

    string SQL_SELECT_ALL = "select * from Sales";
    string SQL_SELECT_BY_SALE_ID = "select * from Sales where SalesID=@id";
    string SQL_INSERT = "insert into Sales(PostingID, SellDate, CustomerID) values (@postingID, @sellDate, @customerID)";
    string SQL_UPDATE = "update Sales set PostingID=@postingID, SellDate=@sellDate, Paid=@paid, CustomerID=@customerID where SalesID=@id";
    string SQL_DELETE = "delete from Sales where SalesID=@id";
    private string SQL_SELECT_SALE_INFO = "select s.SalesId, s.SellDate, s.Paid, u.Fullname, u.Phone from Postings as p join Sales as s on p.PostingID = s.PostingID join UserInfomations as u on s.CustomerID = u.UserID where s.PostingID = @postingID";
    private string SQL_SELECT_SOLD_POSTING = "select p.PostingID, p.PostingName, p.Description, p.Price, p.Url, p.Quotations, s.SellDate, s.Paid, u.Fullname, s.CustomerID from PostingExhibition as pe join Postings as p on p.PostingId = pe.PostingID join Sales as s on s.postingID = p.PostingID"
        + " join UserInfomations as u on s.CustomerID = u.UserID where Status <> 'Selling'";
    private string SQL_SELECT_SALE_iNFORMATION = "select s.SalesID, s.PostingID, s.SellDate, s.Paid, pe.Status, p.PostingName, p.Price, u.Fullname from Postings as p join PostingExhibition as pe on p.PostingID = pe.PostingID join Sales as s on pe.PostingID = s.PostingID join UserInfomations as u on s.CustomerID = u.UserID";
    private string SQL_SELECT_SALE_DETAILS = "select s.SalesID, s.PostingID, s.SellDate, s.Paid, pe.Status, p.PostingName, p.Price, p.Url, p.Quotations, p.Description,  u.Fullname, u.Birthday, u.Phone from Postings as p join PostingExhibition as pe on p.PostingID = pe.PostingID join Sales as s on pe.PostingID = s.PostingID join UserInfomations as u on s.CustomerID = u.UserID where SalesID = @saleID";
    private string SQL_UPDATE_PAID = "update Sales set Paid = @paid where SalesID = @salesID";
    private string SQL_GET3PICTURE_Nearest_Sale = "select TOP(3) s.PostingID, s.SellDate, s.Paid, p.PostingName, p.Price, p.Url from Postings as p join PostingExhibition as pe on p.PostingID = pe.PostingID join Sales as s on pe.PostingID = s.PostingID join UserInfomations as u on s.CustomerID = u.UserID where Paid='true' order by(SellDate) DESC";

    #endregion


    #region fields
    string message;
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion
	public SaleDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public IList<SaleInfo> GetAll()
    {

        IList<SaleInfo> list = new List<SaleInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    SaleInfo sale = new SaleInfo();
                    sale.SalesID = dr.GetInt32(0);
                    sale.PostingID = dr.GetInt32(1);
                    sale.SellDate = dr.GetDateTime(2);
                    sale.Paid = dr.GetBoolean(3);
                    sale.CustomerID = dr.GetString(4);
                    list.Add(sale);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public SaleInfo GetByID(int id)
    {
        SaleInfo sale = new SaleInfo();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_SALE_ID, conn);
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    sale.SalesID = dr.GetInt32(0);
                    sale.PostingID = dr.GetInt64(1);
                    sale.SellDate = dr.GetDateTime(2);
                    sale.Paid = dr.GetBoolean(3);
                    sale.CustomerID = dr.GetString(4);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return sale;
    }

    public int Insert(SaleInfo sale)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@postingID", sale.PostingID);
            command.Parameters.AddWithValue("@sellDate", sale.SellDate);
            command.Parameters.AddWithValue("@customerID", sale.CustomerID);
           
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
            
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Update(SaleInfo sale)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@id", sale.SalesID);
            command.Parameters.AddWithValue("@postingID", sale.PostingID);
            command.Parameters.AddWithValue("@sellDate", sale.SellDate);
            command.Parameters.AddWithValue("@paid", sale.Paid);
            command.Parameters.AddWithValue("@customerID", sale.CustomerID);
            
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    public int Delete(int saleID)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", saleID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }


    public DataSet SelectSaleInfo(Int64 postingID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_SALE_INFO, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@postingID", postingID);
        adapter.Fill(ds);
        return ds;
            
    }


    public DataSet SelectSoldPostings()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_SOLD_POSTING, conn);
        adapter.Fill(ds);
        return ds;
    }


    public DataSet GetSaleInfo()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_SALE_iNFORMATION, conn);
        adapter.Fill(ds);
        return ds;
    }


    public DataSet GetSaleDetails(int saleID)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_SALE_DETAILS, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@saleID", saleID);
        adapter.Fill(ds);
        return ds;
    }


    public int UpdatePaid(SaleInfo sale)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE_PAID, conn);
            command.Parameters.AddWithValue("@salesID", sale.SalesID);
            command.Parameters.AddWithValue("@paid", sale.Paid);  
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
    public DataSet Get3Picture_saleNearest()
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_GET3PICTURE_Nearest_Sale, conn);        
        adapter.Fill(ds);
        return ds;
    }
}